﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace WindowsFormsAcess.OLEDB
{
    /// <summary>
    /// 封装的 OLEDB 操作类 实现最基本的查询操作
    /// </summary>
    public class DBFairy : IDbCommand
    {
        #region 字段和常量
        /// <summary>
        /// ORACLE
        /// </summary>
        public const string DBType_Oracle = "ORACLE";
        /// <summary>
        /// MSSQL
        /// </summary>
        public const string DBType_MSSQL = "MSSQL";
        /// <summary>
        /// ACCESS
        /// </summary>
        public const string DBType_ACCESS = "ACCESS";
        /// <summary>
        /// 数据库类型
        /// </summary>
        private string _DBType = DBType_Oracle;
        /// <summary>
        /// 当然的数据库类型
        /// </summary>
        public string DBType
        {
            get { return _DBType; }
        }
        #endregion

        #region IDbCommand 接口封装
        /// <summary>
        /// 执行的代理
        /// </summary>
        private OleDbCommand dbCommand = new OleDbCommand();
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string _ConnectionString = string.Empty;
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return _ConnectionString; }
        }
        /// <summary>
        /// /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <param name="dbType">数据库类型</param>
        /// <param name="keepAlive">是否保持连接</param>
        public DBFairy(string connectionString, string dbType = DBType_Oracle, bool keepAlive = false)
        {
            // 非默认值检查关键字匹配
            if (!string.IsNullOrEmpty(dbType))
            {
                if (DBType_Oracle != dbType && DBType_MSSQL != dbType && DBType_ACCESS != dbType)
                {
                    throw new NotSupportedException("不受支持的类型描述,请使用 DBFairy.DBType 定义.");
                }
                _DBType = dbType;
            }


            _ConnectionString = connectionString;
            _AlwaysClose = !keepAlive;

            dbCommand.Connection = new OleDbConnection(_ConnectionString);
        }
        public void Cancel()
        {
            throw new NotImplementedException();
        }

        public string CommandText
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery()
        {
            try
            {
                return ExecuteNonQuery(null, null, null);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public int CommandTimeout
        {
            get
            {
                return dbCommand.CommandTimeout;
            }
            set
            {
                dbCommand.CommandTimeout = value;
            }
        }

        public CommandType CommandType
        {
            get
            {
                return dbCommand.CommandType;
            }
            set
            {
                dbCommand.CommandType = value;
            }
        }
        /// <summary>
        /// 连接
        /// </summary>
        public IDbConnection Connection
        {
            get
            {
                return dbCommand.Connection;
            }
            set
            {
                dbCommand.Connection = (OleDbConnection)value;
            }
        }

        public IDbDataParameter CreateParameter()
        {
            return dbCommand.CreateParameter();
        }

        /// <summary>
        /// 执行前检查连接状态 
        /// </summary>
        private void OpenRequired()
        {
            try
            {
                if (dbCommand.Connection.State != ConnectionState.Open)
                {
                    dbCommand.Connection.Open();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// 返回一个阅读器
        /// </summary>
        /// <param name="behavior"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandBehavior behavior)
        {
            try
            {
                OpenRequired();

                return dbCommand.ExecuteReader();
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 返回一个阅读器
        /// </summary>
        /// <returns></returns>
        public IDataReader ExecuteReader()
        {
            try
            {
                return ExecuteReader(CommandBehavior.Default);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public object ExecuteScalar()
        {
            IDbConnection connection = dbCommand.Connection;
            try
            {
                OpenRequired();
                return dbCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (_AlwaysClose && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        /// <summary>
        /// 获取 System.Data.OleDb.OleDbParameterCollection。
        /// </summary>
        public IDataParameterCollection Parameters
        {
            get { return dbCommand.Parameters; }
        }
        /// <summary>
        /// 在数据源上创建该命令的准备好的（或已编译的）版本。
        /// </summary>
        public void Prepare()
        {
            dbCommand.Prepare();
        }
        /// <summary>
        /// 
        /// </summary>
        public IDbTransaction Transaction
        {
            get
            {
                return dbCommand.Transaction;
            }
            set
            {
                dbCommand.Transaction = (OleDbTransaction)value;
            }
        }

        public UpdateRowSource UpdatedRowSource
        {
            get
            {
                return dbCommand.UpdatedRowSource;
            }
            set
            {
                dbCommand.UpdatedRowSource = value;
            }
        }
        /// <summary>
        /// 清理资源
        /// </summary>
        public void Dispose()
        {
            Connection.Close();
            dbCommand.Dispose();
        }
        /// <summary>
        /// 是否保持数据库连接状态
        /// </summary>
        public bool _AlwaysClose { get; set; }
        #endregion

        #region 实用的查询方法
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="strSQl"></param>
        /// <param name="Parameters"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string strSQl, IDbDataParameter[] Parameters = null, IDbTransaction trans = null)
        {
            // 是否关联事务
            if (trans != null)
            {
                dbCommand.Transaction = (OleDbTransaction)trans;
                dbCommand.Connection = (OleDbConnection)trans.Connection;
            }
            // 参数检查
            if (!string.IsNullOrEmpty(strSQl))
                dbCommand.CommandText = strSQl;
            dbCommand.Parameters.Clear();
            if (Parameters != null && Parameters.Length > 0)
                dbCommand.Parameters.AddRange(Parameters);
            IDbConnection connection = dbCommand.Connection;
            try
            {

                OpenRequired();
                return dbCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_AlwaysClose && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        /// <summary>
        ///  执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="strSQl"></param>
        /// <param name="Parameters"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public object ExecuteScalar(string strSQl, IDbDataParameter[] Parameters = null, IDbTransaction trans = null)
        {
            // 是否关联事务
            if (trans != null)
            {
                dbCommand.Transaction = (OleDbTransaction)trans;
                dbCommand.Connection = (OleDbConnection)trans.Connection;
            }
            // 参数检查
            if (!string.IsNullOrEmpty(strSQl))
                dbCommand.CommandText = strSQl;
            dbCommand.Parameters.Clear();
            if (Parameters != null && Parameters.Length > 0)
                dbCommand.Parameters.AddRange(Parameters);



            IDbConnection connection = dbCommand.Connection;
            try
            {

                OpenRequired();
                return dbCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_AlwaysClose && connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }
        /// <summary>
        ///  将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader。
        /// </summary>
        /// <param name="strSQl"></param>
        /// <param name="Parameters"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string strSQl, IDbDataParameter[] Parameters = null, IDbTransaction trans = null)
        {
            // 是否关联事务
            if (trans != null)
            {
                dbCommand.Transaction = (OleDbTransaction)trans;
                dbCommand.Connection = (OleDbConnection)trans.Connection;
            }
            // 参数检查
            if (!string.IsNullOrEmpty(strSQl))
                dbCommand.CommandText = strSQl;
            dbCommand.Parameters.Clear();
            if (Parameters != null && Parameters.Length > 0)
                dbCommand.Parameters.AddRange(Parameters);
            IDbConnection connection = dbCommand.Connection;
            try
            {

                OpenRequired();
                return dbCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region  一些特殊的转换方法
        /// <summary>
        /// 将DotNet平台GUID字符串转换为Oracle中RAW(16)格式
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static string DotNetToOracle(string text)
        {
            Guid guid = new Guid(text);
            return BitConverter.ToString(guid.ToByteArray()).Replace("-", "");
        }
        /// <summary>
        /// 将Oracle中RAW(16)格式转换为 DotNet平台GUID字符串
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static string OracleToDotNet(string text)
        {
            byte[] bytes = ParseHex(text);
            Guid guid = new Guid(bytes);
            return guid.ToString().ToUpperInvariant();
        }
        /// <summary>
        /// 获取十六进制翻译为Byte[]
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static byte[] ParseHex(string text)
        {
            byte[] ret = new byte[text.Length / 2];
            for (int i = 0; i < ret.Length; i++)
            {
                ret[i] = Convert.ToByte(text.Substring(i * 2, 2), 16);
            }
            return ret;
        }
        #endregion
    }
}
